﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using Cosmetic.DataEntity;

namespace Cosmetic.Logic
{
   public class MemberManager
    {
        /// <summary>
        /// Looking for somebody by email.
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
       public static DataSet SearchByEmail(string email, string birthday, string sex, decimal Custome, int FriendNum, string Address)
       {
           string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
           SqlConnection con = new SqlConnection(conStr);
           con.Open();
           string cmdStr = "SELECT [UserId], [UserName], [Email], [Birthday], [NickName], [Password], [Points], [Sex], [Level] , (select Address from UserInfo where [UserInfo].UserId =us1.[UserId] ) as [Address],(select count(1) from [Order] as ord1 where ord1.UserId = us1.[UserId] ) as PayCount,(select sum(Price) from [Order] as ord2 where  ord2.UserId = us1.[UserId] and ord2.State = '完成') as PayAll , (select count(1) from [User] as mu where mu.Referee = us1.[UserId]) as UserCount  ,us1.Integral FROM [User] as us1  where 1=1";
           if (!string.IsNullOrEmpty(email))
           {
               cmdStr += "  and  us1.Email ='" + email + "'";
           }
           if (!string.IsNullOrEmpty(birthday))
           {
               cmdStr += " and us1.Birthday >= '" + birthday + "' and Birthday < '" + Convert.ToDateTime(birthday).AddYears(1).ToString("yyyy/MM/dd") + "' ";
           }
           if (!string.IsNullOrEmpty(sex))
           {
               cmdStr += " and us1.[Sex]= '" + sex + "' ";
           }
           if (Custome > 0)
           {
               cmdStr += " and us1.UserId in (select UserId from [Order] as ord2 where ord2.State = '完成' group by  ord2.UserId having sum(Price)> = " + Custome + " )";
           }
           if (!string.IsNullOrEmpty(Address))
           {
               cmdStr += "  and us1.UserId in (select UserId from UserInfo as ui where ui.Address like '%'+'" + Address + "'+'%') ";
           }
           if (FriendNum > 0)
           {
               cmdStr += " and  us1.UserId in (select us2.Referee from [User] as us2 group by us2.Referee having count(us2.Referee)>= " + FriendNum + " ) ";
           }

           SqlCommand com = new SqlCommand(cmdStr, con);
           SqlDataAdapter dataadapt = new SqlDataAdapter(com);
           DataSet ds = new DataSet();
           dataadapt.Fill(ds);
           con.Close();
           return ds;
       }


       public static string SearchLevelByEmail(string email)
       {
           string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
           SqlConnection con = new SqlConnection(conStr);
           con.Open();
           string cmdStr = "SELECT [Level] FROM [User] where Email ='" + email.Trim() + "'";
           SqlCommand com = new SqlCommand(cmdStr, con);
           string cc = com.ExecuteScalar().ToString();
           con.Close();
           return cc;
       }

       /// <summary>
       /// Get all member information.
       /// </summary>
       /// <returns></returns>
       public static DataSet GetAllData()
       {
           string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
           SqlConnection con = new SqlConnection(conStr);
           con.Open();
           string cmdStr = "SELECT [UserId], [UserName], [Email], [Birthday], [NickName], [Balance],[Password], [Points], [Sex], [Level] , (select Address from UserInfo where [UserInfo].UserId =us1.[UserId] ) as [Address],(select count(1) from [Order] as ord1 where ord1.UserId = us1.[UserId] ) as PayCount,(select sum(Price) from [Order] as ord2 where  ord2.UserId = us1.[UserId]) as PayAll , (select count(1) from [User] as mu where mu.Referee = us1.[UserId]) as UserCount  ,us1.Integral  FROM [User] as us1 ";
           SqlCommand com = new SqlCommand(cmdStr, con);
           SqlDataAdapter dataadapt = new SqlDataAdapter(com);
           DataSet ds = new DataSet();
           dataadapt.Fill(ds);
           con.Close();
           return ds;
       }
       /// <summary>
       /// Update level of member by email.
       /// </summary>
       /// <param name="level"></param>
       /// <param name="email"></param>
       /// <returns></returns>
       public static int UpdateLevelByEmail(string level, string email)
       {
           string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
           SqlConnection con = new SqlConnection(conStr);
           con.Open();
           string cmdStr = "update [User] set [Level]='" + level + "' where  Email ='" + email + "'";
           SqlCommand com = new SqlCommand(cmdStr, con);
           int a = com.ExecuteNonQuery();
           con.Close();
           return a;
       }

       /// <summary>
       /// Modify password. 9.21
       /// </summary>
       /// <param name="level"></param>
       /// <param name="email"></param>
       /// <returns></returns>
       public static bool UpdatePassword(string userId, string password)
       {
           string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
           SqlConnection con = new SqlConnection(conStr);
           try
           {
               con.Open();
               string cmdStr = "update [User] set [Password]='" + password + "' where  [UserId] ='" + userId + "'";
               SqlCommand com = new SqlCommand(cmdStr, con);
               int a = com.ExecuteNonQuery();
               return true;
           }
           catch
           { return false; }
           finally
           {
               con.Close();
           }
       }

       public static bool UpdatePasswords(string userId, string password)
       {
           string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
           SqlConnection con = new SqlConnection(conStr);
           try
           {
               con.Open();
               string cmdStr = "update [xuser] set [mima]='" + password + "' where  [id] ='" + userId + "'";
               SqlCommand com = new SqlCommand(cmdStr, con);
               int a = com.ExecuteNonQuery();
               return true;
           }
           catch
           { return false; }
           finally
           {
               con.Close();
           }
       }
       /// <summary>
       /// Get member's card number.
       /// </summary>
       /// <param name="userId"></param>
       /// <returns></returns>
       public static string GetMemberCard(string userId)
       {
           string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
           SqlConnection con = new SqlConnection(conStr);
           try
           {
               con.Open();
               string cmdStr = "select [MemberCard] from [User] where UserId ='" + userId + "'";
               SqlCommand com = new SqlCommand(cmdStr, con);
               object card = com.ExecuteScalar();
               if (!string.IsNullOrEmpty(card.ToString()))
               {
                   return card.ToString();
               }
               else
               {
                   return "";
               }
           }
           finally
           {
               con.Close();
           }
       }


    }
}
